using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    
    public partial class VLAGPL
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {
            #region
            #endregion
            switch (app.company)
            {
                case "YGR":
                case "TST":
                default:
                    #region
                    dwReturn["LPMANDANT"] = "00";
                    dwReturn["LPLO"] = "01";
                    dwReturn["LPLB"] = "  ";
                    dwReturn["LPPLATZ"] = "        ";
                    dwReturn["LPSTUFE"] = "1";
                    dwReturn["LPKBEZ"] = "                ";
                    dwReturn["LPBEZ"] = "                                                  ";
                    dwReturn["LPINVKZ"] = " ";
                    dwReturn["LPINVDAT"] = "0";
                    dwReturn["LPBUCHBSTDAT"] = "0";
                    dwReturn["LPLOKZ"] = " ";
                    dwReturn["LPLODISPO"] = "J";
                    dwReturn["LPZUTEIL"] = "J";
                    dwReturn["LPVERSLO"] = "01";
                    dwReturn["LPLFSSTK"] = " ";
                    dwReturn["LPDISPKZ"] = " ";
                    dwReturn["LPAUFBKZ"] = " ";
                    dwReturn["LPLBKZ"] = " ";
                    dwReturn["LPAUTO"] = " ";
                    dwReturn["LPSTRTBLG"] = "0";
                    dwReturn["LPBLGEBENE"] = " ";
                    dwReturn["LPANZBLG"] = "0";
                    dwReturn["LPDRUKZ"] = " ";
                    dwReturn["LPKOMMANDO"] = " ";
                    dwReturn["LPSTATUS"] = " ";
                    dwReturn["LPBEL"] = " ";
                    dwReturn["LPKL"] = "  ";
                    dwReturn["LPBLOCK"] = "00";
                    dwReturn["LPKAMAXPA"] = "00";
                    dwReturn["LPBANZPCS"] = "0000";
                    dwReturn["LPBGEBNR"] = "        ";
                    dwReturn["LPBANZPCSG"] = "0000";
                    dwReturn["LPSUBKEN"] = "  ";
                    dwReturn["LPLG"] = "0";
                    dwReturn["LPBRT"] = "0";
                    dwReturn["LPHOE"] = "0";
                    dwReturn["LPMAXGEW"] = "0";
                    dwReturn["LPFUNKT"] = " ";
                    dwReturn["LPVOLB"] = "0";
                    dwReturn["LPANZM"] = "0";
                    dwReturn["LPANZB"] = "0";
                    dwReturn["LPINVDATP"] = "0";
                    dwReturn["LPPARAM1"] = "     ";
                    dwReturn["LPWERK"] = "     ";
                    dwReturn["LPGEWB"] = "000000000";
                    dwReturn["LPKSTKRS"] = "  ";
                    dwReturn["LPKST"] = "0000";
                    dwReturn["LPFILLER"] = "                       ";
                    dwReturn["LPAENDBEN"] = "        ";
                    dwReturn["LPAENDDAT"] = "0";
                    dwReturn["LPNEUBEN"] = "        ";
                    dwReturn["LPNEUDAT"] = "0";
                    dwReturn["LPAENDZL"] = "1";

                    #endregion
                    break;
            }
        }

        public void InsertLAGPL(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            dwData["LPNEUBEN"] = app.User;
            dwData["LPNEUDAT"] = date;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "TST":
                case "YGR":
                    or1.CommandText = "INSERT INTO LAGPL (LPMANDANT,LPLO,LPLB,LPPLATZ,LPSTUFE,LPKBEZ,LPBEZ,LPINVKZ,LPINVDAT,LPBUCHBSTDAT,LPLOKZ,LPLODISPO,LPZUTEIL,LPVERSLO,LPLFSSTK,LPDISPKZ,LPAUFBKZ,LPLBKZ,LPAUTO,LPSTRTBLG,LPBLGEBENE,LPANZBLG,LPDRUKZ,LPKOMMANDO,LPSTATUS,LPBEL,LPKL,LPBLOCK,LPKAMAXPA,LPBANZPCS,LPBGEBNR,LPBANZPCSG,LPSUBKEN,LPLG,LPBRT,LPHOE,LPMAXGEW,LPFUNKT,LPVOLB,LPANZM,LPANZB,LPINVDATP,LPPARAM1,LPWERK,LPGEWB,LPKSTKRS,LPKST,LPFILLER,LPAENDBEN,LPAENDDAT,LPNEUBEN,LPNEUDAT,LPAENDZL)"
                    + " VALUES (:LPMANDANT:,:LPLO:,:LPLB:,:LPPLATZ:,:LPSTUFE:,:LPKBEZ:,:LPBEZ:,:LPINVKZ:,:LPINVDAT:,:LPBUCHBSTDAT:,:LPLOKZ:,:LPLODISPO:,:LPZUTEIL:,:LPVERSLO:,:LPLFSSTK:,:LPDISPKZ:,:LPAUFBKZ:,:LPLBKZ:,:LPAUTO:,:LPSTRTBLG:,:LPBLGEBENE:,:LPANZBLG:,:LPDRUKZ:,:LPKOMMANDO:,:LPSTATUS:,:LPBEL:,:LPKL:,:LPBLOCK:,:LPKAMAXPA:,:LPBANZPCS:,:LPBGEBNR:,:LPBANZPCSG:,:LPSUBKEN:,:LPLG:,:LPBRT:,:LPHOE:,:LPMAXGEW:,:LPFUNKT:,:LPVOLB:,:LPANZM:,:LPANZB:,:LPINVDATP:,:LPPARAM1:,:LPWERK:,:LPGEWB:,:LPKSTKRS:,:LPKST:,:LPFILLER:,:LPAENDBEN:,:LPAENDDAT:,:LPNEUBEN:,:LPNEUDAT:,:LPAENDZL:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break; 
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryLAGPL(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM LAGPL "
                              + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO: AND LPLB=:LPLB: AND LPPLATZ=:LPPLATZ: "
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM LAGPL "
                              + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO: AND LPLB=:LPLB:  "
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM LAGPL "
                              + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO:  "
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "SELECT * FROM LAGPL "
                              + " WHERE LPMANDANT=:LPMANDANT:  "
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "SELECT P.* FROM LAGPL P"
                              + " WHERE LPMANDANT=:LPMANDANT: " + dwData["LPLO"].ToString() + dwData["LPLB"].ToString() + dwData["LPPLATZ"].ToString()
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case -1:
                    or1.CommandText = "SELECT LPLO AS KLO,LPLO||LPLB  AS KLB, LPLO||LPLB||LPPLATZ  AS KLP,LPMANDANT,LPLO,LPLB,LPPLATZ,LPKBEZ AS DES,LPBEZ FROM LAGPL "
                              + " WHERE LPMANDANT=:LPMANDANT: " + dwData["LPLO"].ToString() + dwData["LPLB"].ToString() + dwData["LPPLATZ"].ToString()
                              + " ORDER BY 1,2,3,4";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break; 
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "LAGPL";
            return SResult;
        }
        public void DeleteLAGPL(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM LAGPL   "
                           + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO: AND LPLB=:LPLB: AND LPPLATZ=:LPPLATZ: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

                    break;
                case 2:
                    or1.CommandText = "DELETE FROM LAGPL   "
                           + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO: AND LPLB=:LPLB:  ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    if (app.User.Trim() == "ITXV")
                    {
                        or1.CommandText = "DELETE FROM LAGPL   "
                               + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO:  ";
                        or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    }
                    break;
                case 4:
                    if (app.User.Trim() == "ITXV")
                    {
                        or1.CommandText = "DELETE FROM LAGPL   "
                               + " WHERE LPMANDANT=:LPMANDANT:   ";
                        or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    }
                    break;
                case 5:
                    if (app.User.Trim() == "ITXV")
                    {
                        or1.CommandText = "DELETE FROM LAGPL   ";
                        or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    }
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateLAGPL(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            if (dwData != null)
            {
                dwData["LPAENDBEN"] = app.User;
                dwData["LPAENDDAT"] = date;
            }
            if (option == 0)
            {
                switch (app.company)
                {
                    default: 
                    case "YGR":
                    case "TST":
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE LAGPL SET LPMANDANT=:LPMANDANT:,LPLO=:LPLO:,LPLB=:LPLB:,LPPLATZ=:LPPLATZ:,LPSTUFE=:LPSTUFE:,LPKBEZ=:LPKBEZ:,LPBEZ=:LPBEZ:,LPINVKZ=:LPINVKZ:,LPINVDAT=:LPINVDAT:,LPBUCHBSTDAT=:LPBUCHBSTDAT:,LPLOKZ=:LPLOKZ:,LPLODISPO=:LPLODISPO:,LPZUTEIL=:LPZUTEIL:,LPVERSLO=:LPVERSLO:,LPLFSSTK=:LPLFSSTK:,LPDISPKZ=:LPDISPKZ:,LPAUFBKZ=:LPAUFBKZ:,LPLBKZ=:LPLBKZ:,LPAUTO=:LPAUTO:,LPSTRTBLG=:LPSTRTBLG:,LPBLGEBENE=:LPBLGEBENE:,LPANZBLG=:LPANZBLG:,LPDRUKZ=:LPDRUKZ:,LPKOMMANDO=:LPKOMMANDO:,LPSTATUS=:LPSTATUS:,LPBEL=:LPBEL:,LPKL=:LPKL:,LPBLOCK=:LPBLOCK:,LPKAMAXPA=:LPKAMAXPA:,LPBANZPCS=:LPBANZPCS:,LPBGEBNR=:LPBGEBNR:,LPBANZPCSG=:LPBANZPCSG:,LPSUBKEN=:LPSUBKEN:,LPLG=:LPLG:,LPBRT=:LPBRT:,LPHOE=:LPHOE:,LPMAXGEW=:LPMAXGEW:,LPFUNKT=:LPFUNKT:,LPVOLB=:LPVOLB:,LPANZM=:LPANZM:,LPANZB=:LPANZB:,LPINVDATP=:LPINVDATP:,LPPARAM1=:LPPARAM1:,LPWERK=:LPWERK:,LPGEWB=:LPGEWB:,LPKSTKRS=:LPKSTKRS:,LPKST=:LPKST:,LPFILLER=:LPFILLER:,LPAENDBEN=:LPAENDBEN:,LPAENDDAT=:LPAENDDAT:,LPNEUBEN=:LPNEUBEN:,LPNEUDAT=:LPNEUDAT:,LPAENDZL=:LPAENDZL:"
                        + " WHERE LPMANDANT=:LPMANDANT: AND LPLO=:LPLO: AND LPLB=:LPLB: AND LPPLATZ=:LPPLATZ: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
